import datetime
import re

import pandas as pd
from pandas import DataFrame


def time2month(time: str):
    if time is None or (isinstance(time, float) and pd.isna(time)):
        return ''
    [(day)] = re.findall('^(\d{4}.\d{1,2}.\d{1,2})(?: \d{1,2}:\d{1,2}(?::\d{1,2})?)?$', time)
    return datetime.datetime.strptime(day, '%Y.%m.%d').strftime('%Y/%m')


if __name__ == '__main__':
    excel_path = "aa.xls"
    sheet_name = 'sheet1'
    df: DataFrame = pd.read_excel(excel_path, sheet_name=sheet_name)
    grouped = df.groupby(df['时间'].apply(time2month))
    for name, group in grouped:
        arr = [i + 2 for i in group.index.tolist()]
        if arr[-1] - arr[0] + 1 != len(arr):
            tip = f"{name}数据不对，arr[-1]-arr[0]+1={arr[-1] - arr[0] + 1}, len(arr)={len(arr)}, last(arr)={arr[-1]}, arr={arr}"
            raise ValueError(tip)
        print(f"{name}\t=sum({sheet_name}!F{arr[0]}:{sheet_name}!F{arr[-1]})")
